'''
Created on Aug 2, 2011

@author: Andrey Razhanskiy
'''
import sys
from OFXParser import OFXParser
import sqlite3
import time
import datetime

class FileLoader(object):
    def __init__(self):
        self.db_init()

    def db_init(self):
        self.conn = sqlite3.connect("../data/sb")
        self.cur = self.conn.cursor()
        self.cur.execute('CREATE  TABLE IF NOT EXISTS "main"."accounts" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , "name" TEXT, "type" TEXT, "nick" TEXT, "availbal" DOUBLE, "ledgerbal" DOUBLE, "asof" DATETIME)')
        self.cur.execute('CREATE  TABLE IF NOT EXISTS "main"."transactions" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , "account_id" INTEGER, "fitid" TEXT, "name" TEXT, "amt" DOUBLE, "type" TEXT, "memo" TEXT, "posted" DATETIME)')

    
    def load(self,file_name):
        self.load_stream(file(file_name))

    def load_stream(self,stream):
#        ofx = OFXParser().parse(file('data/capone.QBO'))
        ofx = OFXParser()
        ofx.parse(stream)
        self.save_to_db(ofx)

    def save_to_db(self, ofx):
        acct = self._get_account_id(ofx)
        self._process_transactions(ofx, acct)
        pass

    def _get_account_id(self,ofx):
        acct = ofx.get_account()
        self.cur.execute("select id from accounts where name='%s'" % acct)
        ret = self.cur.fetchone()
        if ret == None:
            type = ofx.get_account_type()
            self.cur.execute("insert into accounts (name, type) values('%s', '%s')" % (acct,type))
            self.conn.commit()
            self.cur.execute("select id from accounts where name='%s'" % acct)
            ret = self.cur.fetchone()
        return ret[0]
    
    def _process_transactions(self, ofx, acct):
        self.cur.execute("select fitid from transactions where account_id='%s'" % acct)
        processed_transactions = [str(p[0]) for p in self.cur.fetchall()]
        for trans in ofx.get_transactions():
            if trans.fitid not in processed_transactions:
                sql = "insert into transactions (account_id, fitid,name,amt, type, memo, posted) values(:account, :fitid, :name, :trnamt, :trntype, :memo, :dtposted)"
                trans.account = acct
                self.cur.execute(sql,trans.__dict__)
        self.conn.commit()
                
        
        
def main(argv):
    fl = FileLoader()
    fl.load("C:/dev/MoneyTracker/data/jpmc2.qbo")    
    fl.load("C:/dev/MoneyTracker/data/activity2.qbo")    

if __name__ == '__main__':
    sys.exit(main(sys.argv))
        